package quanlydoanvao.func3_9;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import javax.faces.model.SelectItem;
import utils.UtilsOnScopes;
import dbutil.ConnectDB;

/******************************************
 * Filename			: F3_9FuncInfo.java
 * Description		: Bean thong tin cua cac chuc nang tu 3 -> 6
 * Created date		: Aug 2, 2008
 * Author			: Phonglt
 ******************************************/
public class F3_9FuncInfo {
	private String error;
	private int type;
	private String content;
	private Date from;
	private Date to;
	private ArrayList<F3_9TableItemInfo> list = new ArrayList<F3_9TableItemInfo>();
	private ArrayList<F3_9MemItemInfo> members = new ArrayList<F3_9MemItemInfo>();
	private F3_9TypeSearch typeSearchInfo = new F3_9TypeSearch();
	/****************************************************
	 * Method name		: getError [GETTER]
	 * Return type		: F3_9FuncInfo
	 * Description		: 
	 * Created date		: Aug 2, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public String getError() {
		return error;
	}
	/****************************************************
	 * Method name		: setError [SETTER]
	 * Return type		: void
	 * Description		: 
	 * Created date		: Aug 2, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public void setError(String error) {
		this.error = error;
	}
	/****************************************************
	 * Method name		: getType [GETTER]
	 * Return type		: F3_9FuncInfo
	 * Description		: 
	 * Created date		: Aug 2, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public int getType() {
		return type;
	}
	/****************************************************
	 * Method name		: setType [SETTER]
	 * Return type		: void
	 * Description		: 
	 * Created date		: Aug 2, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public void setType(int type) {
		this.type = type;
	}
	/****************************************************
	 * Method name		: getContent [GETTER]
	 * Return type		: F3_9FuncInfo
	 * Description		: 
	 * Created date		: Aug 2, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public String getContent() {
		return content;
	}
	/****************************************************
	 * Method name		: setContent [SETTER]
	 * Return type		: void
	 * Description		: 
	 * Created date		: Aug 2, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public void setContent(String content) {
		this.content = content;
	}
	/****************************************************
	 * Method name		: getList [GETTER]
	 * Return type		: F3_9FuncInfo
	 * Description		: 
	 * Created date		: Aug 2, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public ArrayList<F3_9TableItemInfo> getList() {
		this.setList(new ArrayList<F3_9TableItemInfo>());
		// TODO get data
		String typeSearch = this.typeSearchInfo.getFieldSearch(type);
		/*
		 * Trường hợp quốc tịch, so hộ chiếu và giới tính thuộc về thông tin
		 * của thành viên nên không dùng list này để view data ra table mà dùng
		 * getMembers() method.
		*/
		if (typeSearch == "gioitinh" || typeSearch == "sohc")
			return new ArrayList<F3_9TableItemInfo>();
		String SQL = this.generateSQL();
		System.out.println(SQL);
		try {
			ArrayList<HashMap<String, Object>> arr = ConnectDB
					.getDataTypeList(SQL);
			if (arr.size() > 0) {
				ArrayList<F3_9TableItemInfo> items = new ArrayList<F3_9TableItemInfo>();
				for (int i = 0; i < arr.size(); i++) {
					HashMap<String, Object> row = arr.get(i);
					F3_9TableItemInfo item = new F3_9TableItemInfo(i + 1,
							(String) row.get("sovanbanden"), (String) row
									.get("coquandenghi"), (Date) row
									.get("ngayxuatvb"), (String) row
									.get("noidung"), (String) row
									.get("diadiemlamviec"), (Date) row
									.get("ngayden"), (String) row
									.get("linhvucmucdich"), (String) row
									.get("soubndtinh"), (Date) row
									.get("ngayphathanh"), (Boolean) row
									.get("baocao") ? "Có" : "Chưa", (String) row
									.get("nguoichutri"), (Long) row
									.get("songuoiden"));
					item.setMembers(this
							.getMemInfoList((String) row.get("madoan")));
					items.add(item);
				}
				this.setList(items);
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		this.resetData();
		return list;
	}
	/****************************************************
	 * Method name		: setList [SETTER]
	 * Return type		: void
	 * Description		: 
	 * Created date		: Aug 2, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public void setList(ArrayList<F3_9TableItemInfo> list) {
		this.list = list;
	}
	
	/**************************************
	 * Method name		: generateSQL
	 * Return type		: String
	 * Description		: tao ra SQL ung voi type search;
	 * Created date		: Aug 3, 2008
	 * Author			: Phonglt
	 **************************************/
	
	private String generateSQL(){
		String SQL = "SELECT A.id, count(E.id) as songuoiden, A.madoan, A.ngayden, A. ngaydi, A.diadiemlamviec, A.mota,"
				+ " (B.linhvuc || ' - ' || B.mucdich) as linhvucmucdich, C.sovb as sovanbanden,"
				+ " C.coquandenghi, C.ngayxuatvb, C.noidung, D.soubndtinh, D.ngayphathanh, baocao, nguoichutri "
				+ " FROM doanvao A, mucdich B, vanbanden C, vanbannhan D, dsnguoiden E "
				+ " WHERE A.idmucdich = B.id AND A.idvbden = C.id AND A.idvbvao = D.id AND A.madoan = E.iddoanvao ";
		String GROUPBY = " GROUP BY A.id, A.madoan, A.ngayden, A. ngaydi, A.diadiemlamviec, A.mota, linhvucmucdich,"
				+ " sovanbanden, C.coquandenghi, C.ngayxuatvb, C.noidung, D.soubndtinh, D.ngayphathanh, baocao, nguoichutri ";
		String typeSearch = this.typeSearchInfo.getFieldSearch(type);
		if(typeSearch == "ngayden" && this.from != null && this.to != null){
			System.out.println("SQL: date");
			SQL += " AND ngayden BETWEEN '" + formatDate(this.from) + "' AND '" 
			+ formatDate(this.to) + "' ";
			SQL += GROUPBY;
			SQL += " ORDER BY ngayden DESC";
		} else if (typeSearch == "quoctich") {
			// TODO cac truong hop thanh vien
			if (this.content != "")
			SQL += " AND A.madoan in (SELECT iddoanvao FROM dsnguoiden WHERE TRIM(LOWER(" + typeSearch + ")) like TRIM(LOWER('%" + this.content + "%')))";
			SQL += GROUPBY;
			SQL += " ORDER BY ngayden";
		} else if(typeSearch == "gioitinh"){
			//TODO cac truong hop thanh vien
			if(this.content != "")
			SQL += " AND A.id in (SELECT iddoanvao FROM dsnguoiden WHERE gioitinh = " + this.content + ") ";
			SQL += GROUPBY;
			SQL += " ORDER BY ngayden";
		} 
		else if (this.content == null || this.content == "") {
			System.out.println("SQL: default");
			SQL += GROUPBY;
			SQL += " ORDER BY " + typeSearch;
		} else if (typeSearch == "linhvucmucdich"){
			System.out.println("SQL: linh vuc muc dich");
			SQL += " AND (TRIM(LOWER(B.mucdich)) like TRIM(LOWER('%" + this.content + "%'))";
			SQL += " OR TRIM(LOWER(B.linhvuc)) like TRIM(LOWER('%" + this.content + "%')))";
			SQL += GROUPBY;
			SQL += ", linhvuc, mucdich ";
			SQL += " ORDER BY B.linhvuc, B.mucdich" ;
		} else {
			System.out.println("SQL: " + typeSearch);
			SQL += " AND TRIM(LOWER(" + typeSearch + ")) like TRIM(LOWER('%" + this.content + "%'))";
			SQL += GROUPBY;
			SQL += " ORDER BY " + typeSearch;
		}
		
		return SQL;
	}
	
	/****************************************************
	 * Method name		: getFrom [GETTER]
	 * Return type		: F3_9FuncInfo
	 * Description		: 
	 * Created date		: Aug 3, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public Date getFrom() {
		return from;
	}
	/****************************************************
	 * Method name		: setFrom [SETTER]
	 * Return type		: void
	 * Description		: 
	 * Created date		: Aug 3, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public void setFrom(Date from) {
		this.from = from;
	}
	/****************************************************
	 * Method name		: getTo [GETTER]
	 * Return type		: F3_9FuncInfo
	 * Description		: 
	 * Created date		: Aug 3, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public Date getTo() {
		return to;
	}
	/****************************************************
	 * Method name		: setTo [SETTER]
	 * Return type		: void
	 * Description		: 
	 * Created date		: Aug 3, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public void setTo(Date to) {
		this.to = to;
	}
	/**************************************
	 * Method name		: formatDate
	 * Return type		: String
	 * Description		:
	 * Created date		: Aug 3, 2008
	 * Author			: Phonglt
	 **************************************/
	@SuppressWarnings("deprecation")
	private String formatDate(Date date) {
		System.out.println(date);
		return  (date.getYear()+ 1900) + "-" +  (date.getMonth() + 1) + "-" + date.getDate();
	}
	
	/**************************************
	 * Method name		: getMemInfoList
	 * Return type		: ArrayList<F3_9MemTableItemInfo>
	 * Description		:
	 * Created date		: Aug 3, 2008
	 * Author			: Phonglt
	 **************************************/
	public ArrayList<F3_9MemTableItemInfo> getMemInfoList(String idDoanVao){
		String SQL = "SELECT ten, quoctich, gioitinh, sohc, ngaycaphc, " +
				"ngayhethanhc, ngayhethanvisa, nghenghiep " +
				"FROM dsnguoiden WHERE iddoanvao = '" + idDoanVao + "'";
		ArrayList<HashMap<String, Object>> arr = ConnectDB.getDataTypeList(SQL);
		ArrayList<F3_9MemTableItemInfo> mems = new ArrayList<F3_9MemTableItemInfo>();
		if (arr.size() > 0){
			for (HashMap<String, Object> row : arr) {
				F3_9MemTableItemInfo mem = new F3_9MemTableItemInfo(
						(String) row.get("ten"), (String) row.get("quoctich"),
						((String) row.get("gioitinh")),
						(String) row.get("sohc"), (Date) row.get("ngaycaphc"),
						(Date) row.get("ngayhethanhc"), (Date) row
								.get("ngayhethanvisa"), (String) row
								.get("nghenghiep"));
				mems.add(mem);
			}
		}
		return mems;
	}
	
	/**************************************
	 * Method name		: getLabelSearch
	 * Return type		: SelectItem[]
	 * Description		:
	 * Created date		: Aug 3, 2008
	 * Author			: Phonglt
	 **************************************/
	public SelectItem[] getLabelSearch(){
		return this.typeSearchInfo.getLabelSearch();
	}
	
	/**************************************
	 * Method name		: getSrcFormSearch
	 * Return type		: String
	 * Description		:
	 * Created date		: Aug 3, 2008
	 * Author			: Phonglt
	 **************************************/
	public String getSrcFormSearch(){
		System.out.println(this.typeSearchInfo.getSrcFormSearch(this.type));
		return this.typeSearchInfo.getSrcFormSearch(this.type);
	}
	
	/**************************************
	 * Method name		: getSrcTableView
	 * Return type		: String
	 * Description		:
	 * Created date		: Aug 3, 2008
	 * Author			: Phonglt
	 **************************************/
	public String getSrcTableView(){
		System.out.println(this.typeSearchInfo.getSrcTableView(this.type));
		return this.typeSearchInfo.getSrcTableView(this.type);
	}
	
	public int getTongSoDoan(){
		return this.list.size();
	}
	
	/**************************************
	 * Method name		: reset
	 * Return type		: String
	 * Description		: reset session
	 * Created date		: Aug 3, 2008
	 * Author			: Phonglt
	 **************************************/
	public String reset() {
		UtilsOnScopes.getSession().removeAttribute("f3_9FuncInfo");
		return "func3_9";
	}
	
	/**************************************
	 * Method name		: search
	 * Return type		: String
	 * Description		:
	 * Created date		: Aug 3, 2008
	 * Author			: Phonglt
	 **************************************/
	public String search(){
		return "func3_9";
	}
	
	/**************************************
	 * Method name		: getTongSoNguoiVaoLamViec
	 * Return type		: long
	 * Description		:
	 * Created date		: Aug 3, 2008
	 * Author			: Phonglt
	 **************************************/
	public long getTongSoNguoiVaoLamViec(){
		long totalMem = 0;
		for (F3_9TableItemInfo itemTable : this.list) {
			totalMem += itemTable.getSoThanhVien();
		}
		return totalMem;
	}
	/****************************************************
	 * Method name		: getMembers [GETTER]
	 * Return type		: F3_9FuncInfo
	 * Description		: 
	 * Created date		: Aug 5, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public ArrayList<F3_9MemItemInfo> getMembers() {
		// reset data in bean
		this.members = new ArrayList<F3_9MemItemInfo>();
		String SQL = this.generalSQLOfMembers();
		System.out.println(SQL);
		try {
			ArrayList<HashMap<String, Object>> arr = ConnectDB
					.getDataTypeList(SQL);
			if (arr.size() > 0) {
				ArrayList<F3_9MemItemInfo> members = new ArrayList<F3_9MemItemInfo>();
				for (int i = 0; i < arr.size(); i++) {
					HashMap<String, Object> row = arr.get(i);
					F3_9MemItemInfo mem = new F3_9MemItemInfo(i + 1,
							(String) row.get("ten"), (String) row
									.get("namsinh"), (String) row
									.get("gioitinh"),
							(String) row.get("quoctich"), (String) row
									.get("sohc"), (Date) row.get("ngaycaphc"),
							(Date) row.get("ngayhethanhc"), (Date) row
									.get("ngayhethanvisa"), (String) row
									.get("nghenghiep"), (String) row
									.get("diadiemlamviec"), (String) row
									.get("tglamviec"), (String) row
									.get("linhvucmucdich"), (String) row
									.get("coquandenghi"), (Long) row
									.get("count"));
					members.add(mem);
				}
				this.setMembers(members);
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		this.resetData();
		return members;
	}
	/****************************************************
	 * Method name		: setMembers [SETTER]
	 * Return type		: void
	 * Description		: 
	 * Created date		: Aug 5, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public void setMembers(ArrayList<F3_9MemItemInfo> members) {
		this.members = members;
	}
	
	/**************************************
	 * Method name		: generalSQLOfMembers
	 * Return type		: String
	 * Description		: 
	 * Created date		: Aug 5, 2008
	 * Author			: Phonglt
	 **************************************/
	private String generalSQLOfMembers(){
		String SQL = "SELECT A.ten, A.sohc, A.ngaycaphc, A.ngayhethanhc, " +
				"ngayhethanvisa, A.quoctich, A.nghenghiep, A.gioitinh, " +
				"A.namsinh, F.count , B.diadiemlamviec, " +
				"('từ ' || B.ngayden  || ' đến ' || B.ngaydi) as tglamviec, " +
				"(E.linhvuc || ' - ' || E.mucdich) as linhvucmucdich,  C.coquandenghi " +
				"FROM dsnguoiden A, doanvao B, vanbanden C, mucdich E, " +
				"(SELECT ten, count(id) FROM dsnguoiden GROUP BY ten) as F " +
				"WHERE A.iddoanvao = B.madoan AND B.idvbden = C.id AND B.idmucdich = E.id AND A.ten = F.ten ";
		String typeSearch = this.typeSearchInfo.getFieldSearch(type);
		if (typeSearch == "gioitinh") {
			System.out.println("content: " + this.content);
			if (this.content != null
					&& (this.content.equals("Nam") || this.content
							.equals("Nữ")))
				SQL += " AND gioitinh = '" + this.content + "'";
			SQL += "  ORDER BY gioitinh";
		} else if (typeSearch == "sohc" || typeSearch == "quoctich") {
			if (this.content != null && this.content.trim() != "")
				SQL += " AND (TRIM(LOWER(" + typeSearch
						+ ")) like TRIM(LOWER('%" + this.content + "%')))";
			SQL += "ORDER BY " + typeSearch;
		}
		return SQL;
	}
	
	/**************************************
	 * Method name		: getNumOfMembers
	 * Return type		: int
	 * Description		:
	 * Created date		: Aug 5, 2008
	 * Author			: Phonglt
	 **************************************/
	public int getNumOfMembers(){
		return this.members.size();
	}
	
	/**************************************
	 * Method name		: resetData
	 * Return type		: void
	 * Description		:
	 * Created date		: Aug 5, 2008
	 * Author			: Phonglt
	 **************************************/
	private void resetData(){
		this.setContent("");
		this.setFrom(null);
		this.setTo(null);
	}
}
